﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

/// <summary>
/// Summary description for Customer
/// </summary>
public class Customer
{
    public Customer()
    {
    }


    public string firstName { set; get; }
    public string lastName { set; get; }
    public string nric { set; get; }
    public string nationality { set; get; }
    public DateTime dob { set; get; }
    public string gender { set; get; }
    public string smoker { set; get; }
    public string maritalStatus { set; get; }
    public string mailingAddress { set; get; }
    public string postalCode { set; get; }
    public string mobileNo { set; get; }
    public string telNo { set; get; }
    public string email { set; get; }
    public string occupation { set; get; }
    public string jobDuties { set; get; }
    public string employer { set; get; }
    public decimal? noOfYears { set; get; }
    public string industry { set; get; }
    public decimal? annualIncomeCurrent { set; get; }
    public decimal? annualIncomePrevious { set; get; }
    public String otherIncomeSource { set; get; }
    public decimal? otherSourceIncome { set; get; }
    public string sourceOfWealth { set; get; }
    public string bankruptcy { set; get; }
    public string benefitType { set; get; }
    public decimal? basicSumInsured { set; get; }
    public decimal? plusRiderSumInsured { set; get; }
    public decimal? waiverRiderPremium { set; get; }
    public decimal? totalAnnualPremium { set; get; }
    public string premiumFrequency { set; get; }
    public string paymentMethod { set; get; }
    public string autoRebalancing { set; get; }
    public List<Fund> fundAllocation { set; get; }

    public int userId { set; get; }
    public int accountId { set; get; }
    public int staffId { set; get; }
    public DateTime issueDate { set; get; }
    public string filePath { set; get; }

    Certificate certificate = new Certificate();

    public static Customer getCustomerInfo(string nric)
    {
        Customer customer = new Customer();
        var conn = DBManager.getSqlConnection();
        conn.Open();
        string sql = "SELECT firstName, lastName, nric, nationality, dob, gender, smoker, mailingAddress, postalCode, mobileNo, telNo, email," +
                     "occupation, jobDuties, employer, noOfYears, industry, annualIncomeCurrent, annualIncomePrevious, otherIncomeSource," +
                     "otherSourceIncome, sourceOfWealth, bankruptcy, maritalStatus, userID" +
                     " FROM CUSTOMER WHERE nric = '" + nric + "'";

        var cmd = new SqlCommand(sql, conn);

        SqlDataReader reader = cmd.ExecuteReader();

        if (reader.Read())
        {
            customer.firstName = reader.GetString(0);
            customer.lastName = reader.GetString(1);
            customer.nric = reader.GetString(2);
            customer.nationality = reader.GetString(3);
            customer.dob = reader.GetDateTime(4);
            customer.gender = reader.GetString(5);
            customer.smoker = reader.GetString(6);
            customer.mailingAddress = reader.GetString(7);
            customer.postalCode = reader.GetString(8);
            customer.mobileNo = reader.GetString(9);
            customer.telNo = reader.GetString(10);
            customer.email = reader.GetString(11);
            customer.occupation = reader.GetString(12);
            customer.jobDuties = reader.GetString(13);
            customer.employer = reader.GetString(14);
            customer.noOfYears = reader.GetDecimal(15);
            customer.industry = reader.GetString(16);
            customer.annualIncomeCurrent = reader.GetDecimal(17);
            customer.annualIncomePrevious = reader.GetDecimal(18);
            customer.otherIncomeSource = reader.IsDBNull(19) ? "" : reader.GetString(19);
            customer.otherSourceIncome = reader.GetDecimal(20);
            customer.sourceOfWealth = reader.GetString(21);
            customer.bankruptcy = reader.GetString(22);
            customer.maritalStatus = reader.GetString(23);
            customer.userId = reader.GetInt32(24);
        }

        reader.Close();

        conn.Close();

        return customer;

    }

    public static Customer getCustomerInfo(int userId)
    {
        Customer customer = new Customer();
        var conn = DBManager.getSqlConnection();
        conn.Open();
        string sql = "SELECT firstName, lastName, nric, nationality, dob, gender, smoker, mailingAddress, postalCode, mobileNo, telNo, email," +
                     "occupation, jobDuties, employer, noOfYears, industry, annualIncomeCurrent, annualIncomePrevious, otherIncomeSource," +
                     "otherSourceIncome, sourceOfWealth, bankruptcy, maritalStatus,userId" +
                     " FROM CUSTOMER WHERE userId = '" + userId + "'";

        var cmd = new SqlCommand(sql, conn);

        SqlDataReader reader = cmd.ExecuteReader();

        if (reader.Read())
        {
            customer.firstName = reader.GetString(0);
            customer.lastName = reader.GetString(1);
            customer.nric = reader.GetString(2);
            customer.nationality = reader.GetString(3);
            customer.dob = reader.GetDateTime(4);
            customer.gender = reader.GetString(5);
            customer.smoker = reader.GetString(6);
            customer.mailingAddress = reader.GetString(7);
            customer.postalCode = reader.GetString(8);
            customer.mobileNo = reader.GetString(9);
            customer.telNo = reader.GetString(10);
            customer.email = reader.GetString(11);
            customer.occupation = reader.GetString(12);
            customer.jobDuties = reader.GetString(13);
            customer.employer = reader.GetString(14);
            customer.noOfYears = reader.GetDecimal(15);
            customer.industry = reader.GetString(16);
            customer.annualIncomeCurrent = reader.GetDecimal(17);
            customer.annualIncomePrevious = reader.GetDecimal(18);
            customer.otherIncomeSource = reader.GetString(19);
            customer.otherSourceIncome = reader.GetDecimal(20);
            customer.sourceOfWealth = reader.GetString(21);
            customer.bankruptcy = reader.GetString(22);
            customer.maritalStatus = reader.GetString(23);
            customer.userId = reader.GetInt32(24);
        }

        reader.Close();

        conn.Close();

        return customer;

    }

    public void updateCustomerInfo()
    {
         string sql  = "UPDATE CUSTOMER "
                 + "SET firstName=@firstName"
                 + ",lastName=@lastName"
                 + ",nric=@nric"
                 + ",nationality=@nationality"
                 + ",dob=@dob"
                 + ",gender=@gender"
                  + ",maritalStatus=@maritalStatus"
                 + ",smoker=@smoker"
                 + ",mailingAddress=@mailingAddress"
                 + ",postalCode=@postalCode"
                 + ",mobileNo=@mobileNo"
                 + ",telNo=@telNo"
                 + ",email=@email"
                 + ",occupation=@occupation"
                 + ",jobDuties=@jobDuties"
                 + ",employer=@employer"
                 + ",noOfYears=@noOfYears"
                 + ",industry=@industry"
                 + ",annualIncomeCurrent=@annualIncomeCurrent"
                 + ",annualIncomePrevious=@annualIncomePrevious"
                 + ",otherIncomeSource=@otherIncomeSource"
                 + ",otherSourceIncome=@otherSourceIncome"
                 + ",sourceOfWealth=@sourceOfWealth"
                 + ",bankruptcy=@bankruptcy"
                 + " WHERE userId = @userId";

         /* 
       string sql  =  "UPDATE CUSTOMER "
              + "SET firstName='"+ firstName + "'"
              + ",lastName='"+ lastName + "'"
              + ",nric='"+ nric + "'"
              + ",nationality='"+ nationality + "'"
              + ",dob=CONVERT(DATETIME,'" + String.Format("{0:dd/MM/yyyy}", dob) + "')"
              + ",gender='"+ gender + "'"
              + ",maritalStatus='"+ maritalStatus + "'"
              + ",smoker='"+ smoker + "'"
              + ",mailingAddress='"+ mailingAddress + "'"
              + ",postalCode='"+ postalCode + "'"
              + ",mobileNo='"+ mobileNo + "'"
              + ",telNo='"+ telNo + "'"
              + ",email='"+ email + "'"
              + ",occupation='"+ occupation + "'"
              + ",jobDuties='"+ jobDuties + "'"
              + ",employer='"+ employer + "'"
              + ",noOfYears="+ noOfYears + ""
              + ",industry='"+ industry + "'"
              + ",annualIncomeCurrent="+ annualIncomeCurrent + ""
              + ",annualIncomePrevious="+ annualIncomePrevious + ""
              + ",otherIncomeSource='"+ otherIncomeSource + "'"
              + ",otherSourceIncome="+ otherSourceIncome + ""
              + ",sourceOfWealth='"+ sourceOfWealth + "'"
              + ",bankruptcy='"+ bankruptcy + "'"
              + " WHERE userId = "+ userId;
        */
          var conn = DBManager.getSqlConnection();
          conn.Open();
          var cmd = new SqlCommand(sql, conn);

        
        cmd.Parameters.AddWithValue("@firstName", firstName);
        cmd.Parameters.AddWithValue("@lastName", lastName);
        cmd.Parameters.AddWithValue("@nric", nric);
        cmd.Parameters.AddWithValue("@nationality", nationality);
        cmd.Parameters.AddWithValue("@dob", (object)dob ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@gender", gender);
        cmd.Parameters.AddWithValue("@maritalStatus", maritalStatus);
        cmd.Parameters.AddWithValue("@smoker", smoker);
        cmd.Parameters.AddWithValue("@mailingAddress", mailingAddress);
        cmd.Parameters.AddWithValue("@postalCode", postalCode);
        cmd.Parameters.AddWithValue("@mobileNo", mobileNo);
        cmd.Parameters.AddWithValue("@telNo", telNo);
        cmd.Parameters.AddWithValue("@email", email);
        cmd.Parameters.AddWithValue("@occupation", occupation);
        cmd.Parameters.AddWithValue("@jobDuties", jobDuties);
        cmd.Parameters.AddWithValue("@employer", employer);
        cmd.Parameters.AddWithValue("@noOfYears", (object)noOfYears ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@industry", industry);
        cmd.Parameters.AddWithValue("@annualIncomeCurrent", (object)annualIncomeCurrent ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@annualIncomePrevious", (object)annualIncomePrevious ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@otherIncomeSource", (object)otherIncomeSource ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@otherSourceIncome", (object)otherSourceIncome ?? 0m);
        cmd.Parameters.AddWithValue("@sourceOfWealth", sourceOfWealth);
        cmd.Parameters.AddWithValue("@bankruptcy", bankruptcy);
        cmd.Parameters.AddWithValue("@userId", userId);
        
        cmd.ExecuteNonQuery();

        conn.Close();

    }
}

    